{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ff1997ae",
   "metadata": {},
   "source": [
    "## Retrieving And Updating Data Contained in Shelve in Python\n",
    "\n",
    "* In Python shelve you access the keys randomly. In order to access the keys randomly in python shelve we use open() function. \n",
    "\n",
    "* This function works a lot like the file open() function in File handling. Syntax for open the file using Python shelve"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc6a662a",
   "metadata": {},
   "source": [
    "shelve.open(filename, flag='c' , writeback=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2aa82535",
   "metadata": {},
   "source": [
    "In Order to access the keys randomly in shelve in Python, we have to take three steps:\n",
    "* Storing Python shelve data\n",
    "* Retrieving Python shelve data\n",
    "* Updating Python shelve data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "06389da0",
   "metadata": {},
   "source": [
    "### Storing Python shelve data :\n",
    "\n",
    "In order to store python shelve data, we have to create a file with full of datasets and open them with a open() function this function open a file which we have created.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c41fe5ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "# At first, we have to import the 'Shelve' module.\n",
    "import shelve\n",
    "  \n",
    "# In this step, we create a shelf file.\n",
    "shfile = shelve.open(\"shelf_file\")\n",
    "  \n",
    "# we create a data object which in this case is a book_list.\n",
    "my_book_list =['bared_to_you', 'The_fault_in_our_stars',\n",
    "              'The_boy_who_never_let_her_go']\n",
    "  \n",
    "# we are assigning a dictionary key to the list \n",
    "# which we will want to retrieve\n",
    "shfile['book_list']= my_book_list\n",
    "  \n",
    "# now, we simply close the shelf file.\n",
    "shfile.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ff79b2bc",
   "metadata": {},
   "source": [
    "### Retrieving Python shelve data :\n",
    "After storing a shelve data, we have to retrieve some data from a file in order to do that we use index operator [] as we do in lists and in many other data types."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "db741db8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['bared_to_you', 'The_fault_in_our_stars', 'The_boy_who_never_let_her_go', 'who move my cheese', 'Hush', 'The breif history of humankind']\n"
     ]
    }
   ],
   "source": [
    "# At first, we import the 'Shelve' module.\n",
    "import shelve\n",
    "  \n",
    "# In this step, we create a shelf file.\n",
    "var = shelve.open(\"shelf_file\")\n",
    "  \n",
    "# Now, this 'var' variable points to all the \n",
    "# data objects in the file 'shelf_file'.\n",
    "print(var['book_list'])\n",
    "  \n",
    "# now, we simply close the file 'shelf_file'.\n",
    "var.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3aa1ef7b",
   "metadata": {},
   "source": [
    "### Updating Python shelve data :\n",
    "In order to update a python shelve data, we use append() function or we can easily update as we do in lists and in other data types. In order to make our changes permanent we use sync() function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e85226b8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Enter the number of values 3\n",
      "\n",
      " Enter the value\twho move my cheese\n",
      "\n",
      " Enter the value\tHush\n",
      "\n",
      " Enter the value\tThe breif history of humankind\n",
      "['bared_to_you', 'The_fault_in_our_stars', 'The_boy_who_never_let_her_go', 'who move my cheese', 'Hush', 'The breif history of humankind']\n"
     ]
    }
   ],
   "source": [
    "# At first, we have to import the 'Shelve' module.\n",
    "import shelve\n",
    "  \n",
    "# In this step, we create a shelf file.\n",
    "var = shelve.open(\"shelf_file\", writeback = True)\n",
    "  \n",
    "# inputting total values we want to add \n",
    "# to the already existing list in shelf_file.\n",
    "val1 = int(input(\"Enter the number of values \"))\n",
    "  \n",
    "for x in range(val1):\n",
    "      \n",
    "   val = input(\"\\n Enter the value\\t\")\n",
    "     \n",
    "   var['book_list'].append(val)\n",
    "  \n",
    "# Now, this 'var' variable will help in printing\n",
    "# the data objects in the file 'shelf_file'.\n",
    "print(var['book_list'])\n",
    "  \n",
    "# to make our changes permanent, we use \n",
    "# synchronize function.\n",
    "var.sync()\n",
    "  \n",
    "# now, we simply close the file 'shelf_file'.\n",
    "var.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4323a36e",
   "metadata": {},
   "source": [
    "## SQLite\n",
    "\n",
    "SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language. Some applications can use SQLite for internal data storage. It’s also possible to prototype an application using SQLite and then port the code to a larger database such as PostgreSQL or Oracle.\n",
    "\n",
    "The sqlite3 module was written by Gerhard Häring. It provides a SQL interface compliant with the DB-API 2.0 specification described by PEP 249, and requires SQLite 3.7.15 or newer.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ce46f2bb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "id": "ee36e8bb",
   "metadata": {},
   "source": [
    "To use the module, you must first create a Connection object that represents the database. Here the data will be stored in the example.db file:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46e2b625",
   "metadata": {},
   "source": [
    "## Create Database"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e1664c31",
   "metadata": {},
   "outputs": [],
   "source": [
    "import sqlite3\n",
    "con = sqlite3.connect(\"example.db\", [,timeout ,other optional arguments])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3805bd40",
   "metadata": {},
   "source": [
    "该 API 打开一个到 SQLite 数据库文件 database 的链接。您可以使用 \":memory:\" 来在 RAM 中打开一个到 database 的数据库连接，而不是在磁盘上打开。如果数据库成功打开，则返回一个连接对象。\n",
    "当一个数据库被多个连接访问，且其中一个修改了数据库，此时 SQLite 数据库被锁定，直到事务提交。timeout 参数表示连接等待锁定的持续时间，直到发生异常断开连接。timeout 参数默认是 5.0（5 秒）。\n",
    "如果给定的数据库名称 filename 不存在，则该调用将创建一个数据库。如果您不想在当前目录中创建数据库，那么您可以指定带有路径的文件名，这样您就能在任意地方创建数据库。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "234ca1aa",
   "metadata": {},
   "source": [
    "## Create Table\n",
    "\n",
    "__CREATE TABLE__ in SQLite can used to create a new table in any datebase.   \n",
    "* The basic syntax of creating table is as following"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "533a87db",
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE database_name.table_name(\n",
    "   column1 datatype  PRIMARY KEY(one or more columns),\n",
    "   column2 datatype,\n",
    "   column3 datatype,\n",
    "   .....\n",
    "   columnN datatype,\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6c997466",
   "metadata": {},
   "source": [
    "* __an example__"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "79171970",
   "metadata": {},
   "outputs": [],
   "source": [
    "CREATE TABLE COMPANY(\n",
    "   ID INT PRIMARY KEY     NOT NULL,\n",
    "   NAME           TEXT    NOT NULL,\n",
    "   AGE            INT     NOT NULL,\n",
    "   ADDRESS        CHAR(50),\n",
    "   SALARY         REAL\n",
    ");"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "06d85a3e",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Delete Table\n",
    "\n",
    "__DROP TABLE__ in SQLite can used to create a new table in any datebase.   \n",
    "* The basic syntax of creating table is as following"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59f03ef7",
   "metadata": {},
   "source": [
    "* Once you have a Connection, you can create a __Cursor object__ and call its __execute()__ method to perform SQL commands:"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e0e48e1",
   "metadata": {},
   "source": [
    "__connection.cursor([cursorClass])__\n",
    "该例程创建一个 cursor，将在 Python 数据库编程中用到。该方法接受一个单一的可选的参数 cursorClass。如果提供了该参数，则它必须是一个扩展自 sqlite3.Cursor 的自定义的 cursor 类。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da1a461f",
   "metadata": {},
   "source": [
    "__cursor.execute(sql [, optional parameters])__\n",
    "\n",
    "该例程执行一个 SQL 语句。该 SQL 语句可以被参数化（即使用占位符代替 SQL 文本）。sqlite3 模块支持两种类型的占位符：问号和命名占位符（命名样式）。\n",
    "* 例如：cursor.execute(\"INSERT INTO people VALUES (?, ?)\", (who, age))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "944fe485",
   "metadata": {},
   "outputs": [],
   "source": [
    "cur = con.cursor()\n",
    "\n",
    "# Create table\n",
    "cur.execute('''CREATE TABLE stocks\n",
    "               (date text, trans text, symbol text, qty real, price real)''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "63ecaf6a",
   "metadata": {},
   "source": [
    "__cursor.executemany(sql, seq_of_parameters)__  \n",
    "该例程对 seq_of_parameters 中的所有参数或映射执行一个 SQL 命令。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ed98e0c",
   "metadata": {},
   "source": [
    "__cursor.executescript(sql_script)__  \n",
    "该例程一旦接收到脚本，会执行多个 SQL 语句。它首先执行 COMMIT 语句，然后执行作为参数传入的 SQL 脚本。所有的 SQL 语句应该用分号 ; 分隔"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe31513a",
   "metadata": {},
   "source": [
    "__connection.total_changes()__  \n",
    "该例程返回自数据库连接打开以来被修改、插入或删除的数据库总行数。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19b6e9ae",
   "metadata": {},
   "source": [
    "__connection.commit()__  \n",
    "该方法提交当前的事务。如果您未调用该方法，那么自您上一次调用 commit() 以来所做的任何动作对其他数据库连接来说是不可见的。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b30f889f",
   "metadata": {},
   "source": [
    "__connection.rollback()__  \n",
    "该方法回滚自上一次调用 commit() 以来对数据库所做的更改。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "31a7f6b9",
   "metadata": {},
   "source": [
    "__connection.close()__  \n",
    "该方法关闭数据库连接。请注意，这不会自动调用 commit()。如果您之前未调用 commit() 方法，就直接关闭数据库连接，您所做的所有更改将全部丢失！"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7489504b",
   "metadata": {},
   "source": [
    "__cursor.fetchone()__  \n",
    "该方法获取查询结果集中的下一行，返回一个单一的序列，当没有更多可用的数据时，则返回 None。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8f359010",
   "metadata": {},
   "source": [
    "__cursor.fetchmany([size=cursor.arraysize])__  \n",
    "该方法获取查询结果集中的下一行组，返回一个列表。当没有更多的可用的行时，则返回一个空的列表。该方法尝试获取由 size 参数指定的尽可能多的行。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43d56d03",
   "metadata": {},
   "source": [
    "__cursor.fetchall()__  \n",
    "该例程获取查询结果集中所有（剩余）的行，返回一个列表。当没有可用的行时，则返回一个空的列表。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0aac4595",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
